package DAO;

import java.sql.*;
import javax.swing.*;
import connect.ConnectDB;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;

public class VacancyFillDAO {

    Connection conn;
    ResultSet rsVacFill = null;
    ResultSet rsJob = null;
    Statement st;
    PreparedStatement pst;
    ConnectDB cdb;

    public VacancyFillDAO() {
        cdb = new ConnectDB();
        cdb.getConnect();
        this.conn = cdb.conn;

        loadDataVacFill();
    }

    public void loadDataVacFill() {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             VacancyFillDetails";
            st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rsVacFill = st.executeQuery(query);
        } catch (Exception e) {
            System.out.println("Error in loadDataVacFill(): " + e.getMessage());
        }
    }

    public DefaultTableModel loadTableVacFill() {
        try {
            rsVacFill.beforeFirst();

            ResultSetMetaData rsmd = rsVacFill.getMetaData();
            int noCol = rsmd.getColumnCount();

            Vector colName = new Vector();
            for (int i = 1; i <= noCol; i++) {
                colName.add(rsmd.getColumnName(i));
            }
            DefaultTableModel dtm = new DefaultTableModel(colName, 0);

            while (rsVacFill.next()) {
                Vector rowData = new Vector();
                for (int i = 1; i <= noCol; i++) {
                    if (i == 5) {
                        if (rsVacFill.getString("STATUS").toString().equals("1")) {
                            rowData.add("Confirmed");
                        } else if (rsVacFill.getString("STATUS").toString().equals("0")) {
                            rowData.add("Not Confirmed");
                        } else {
                            rowData.add("Deny");
                        }
                    }
                    rowData.add(rsVacFill.getString(i));
                }
                dtm.addRow(rowData);
            }
            return dtm;
        } catch (Exception e) {
            System.out.println("Error in loadTableVacFill: " + e.getMessage());
            return null;
        }
    }

    public Vector loadVacFillToUpdate(String vacId, String empId) {
        try {
            Vector vecData = new Vector();
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             VacancyFillDetails "
                    + "     WHERE "
                    + "             VACANCYID = ? "
                    + "     AND "
                    + "             EMPID = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setString(1, vacId);
            pst.setString(2, empId);

            ResultSet rs = pst.executeQuery();
            rs.next();
            vecData.add(rs.getString("FILLEDDATE"));
            vecData.add(rs.getString("INTAKEDETAILS"));
            pst.close();
            rs.close();
            return vecData;
        } catch (Exception e) {
            System.out.println("Error in loadVacFillToUpdate: " + e.getMessage());
            return null;
        }
    }

    public Vector loadVacFillEmpInfoUpdate(String vacId, String empId) {
        try {
            Vector vecData = new Vector();
            return vecData;
        } catch (Exception e) {
            System.out.println("Error in loadVacFillEmpInfoUpdate: " + e.getMessage());
            return null;
        }
    }

    public String checkVacFillId(String vacId, String empId) {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             VacancyFillDetails "
                    + "     WHERE "
                    + "             VACANCYID = ? "
                    + "     AND "
                    + "             EMPID = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setString(1, vacId);
            pst.setString(2, empId);
            ResultSet rs = pst.executeQuery();
            if (!rs.next()) {
                pst.close();
                rs.close();
                return "";
            }
            pst.close();
            rs.close();
            return "Exist";
        } catch (Exception e) {
            System.out.println("Error in checkVacFillId: " + e.getMessage());
            return "";
        }
    }

    public boolean insertVacFill(Vector vacFillData) {
        try {
            CallableStatement cst = conn.prepareCall("{call insVacFill(?,?,?,?)}");
            cst.setString(1, vacFillData.get(0).toString());
            cst.setString(2, vacFillData.get(1).toString());
            cst.setString(3, vacFillData.get(2).toString());
            cst.setString(4, vacFillData.get(3).toString());
            if (cst.executeUpdate() != -1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            System.out.println("Error in insertVacFill: " + e.getMessage());
            return false;
        }
    }

    public void insertToJob(Vector v) {
        try {
            String empId = v.get(1).toString();
            String vacId = v.get(0).toString();
            String desName = getDesName(empId);

            String query = "Select * from JobRotation";
            Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rsJob = st.executeQuery(query);

            rsJob.moveToInsertRow();
            rsJob.updateString(1, empId);
            rsJob.updateString(2, desName);
            rsJob.updateString(3, "");
            rsJob.updateInt(4, 0);
            rsJob.updateString(5, "BBB");
            rsJob.insertRow();

        } catch (Exception e) {
            System.out.println("Error in insertToJob: " + e.getMessage());
        }
    }

    public boolean updateVacFill(Vector v) {
        try {
            rsVacFill.beforeFirst();
            while (rsVacFill.next()) {
                if (v.get(0).toString().endsWith(rsVacFill.getString("VACANCYID"))) {
                    rsVacFill.updateString(1, v.get(0).toString());
                    rsVacFill.updateString(2, v.get(1).toString());
                    rsVacFill.updateString(3, v.get(2).toString());
                    rsVacFill.updateString(4, v.get(3).toString());
                    rsVacFill.updateRow();
                    loadDataVacFill();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Error in updateVacFill: " + e.getMessage());
            return false;
        }
    }

    public String isHaveVacNoConfirm(String empId) {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             VacancyFillDetails "
                    + "     WHERE "
                    + "             EMPID = ? "
                    + "     AND "
                    + "             STATUS = '0'";
            pst = conn.prepareStatement(query);
            pst.setString(1, empId);
            ResultSet rs = pst.executeQuery();

            if (rs.next()) {
                System.out.println("Vac id: " + rs.getString("VACANCYID"));
                return rs.getString("VACANCYID");
            }

            return "";
        } catch (Exception e) {
            System.out.println("Error in checkEmpId: " + e.getMessage());
            return "";
        }
    }

    public String checkVacFillStatus(String vacId, String empId) {
        try {
            String query = "SELECT "
                    + "             STATUS "
                    + "     FROM "
                    + "             VacancyFillDetails "
                    + "     WHERE   "
                    + "             VACANCYID = ? "
                    + "     AND"
                    + "             EMPID = ? ";
            pst = conn.prepareStatement(query);
            pst.setString(1, vacId);
            pst.setString(2, empId);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                if (rs.getString("STATUS").equals("0")) {
                    return "Not confirmed";
                }
                if (rs.getString("STATUS").equals("1")) {
                    return "Confirmed";
                }
                return "Deny";
            }

            return "New code";
        } catch (Exception e) {
            System.out.println("Error in checkVacFillStatus: " + e.getMessage());
            return "";
        }
    }

    public String getEmpId(String username) {
        try {
            String query = "select EMPID from Account where USERNAME = ?";
            pst = conn.prepareStatement(query);
            pst.setString(1, username);
            ResultSet rs = pst.executeQuery();
            rs.next();
            return rs.getString("EMPID").trim();
        } catch (Exception e) {
            System.out.println("Error in getEmpId: " + e.getMessage());
            return "";
        }
    }

    public boolean deleteVacFill(String empId) {
        try {
            rsVacFill.beforeFirst();
            while (rsVacFill.next()) {
                if (empId.equals(rsVacFill.getString("EMPID"))) {
                    rsVacFill.deleteRow();
                    loadDataVacFill();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Error in deleteVacFill: " + e.getMessage());
            return false;
        }
    }

    public String getDesName(String empId) {
        try {
            String query = "select * from Employees as e join Designation as d on EMPID = ? and e.DESIGNID = d.DESIGNID";
            pst = conn.prepareStatement(query);
            pst.setString(1, empId);
            ResultSet rs = pst.executeQuery();
            rs.next();
            return rs.getString("DESIGNATION");
        } catch (Exception e) {
            System.out.println("Error in getDes: " + e.getMessage());
            return "";
        }
    }
    
    public void closeConnect() {
        try {
            if (rsVacFill != null) {
                rsVacFill.close();
            }
            
            if (pst != null){
                pst.close();
            }
            
            if (st!= null) {
                st.close();
            }
            
            if (conn!=null){
                conn.close();
            }
            
            if (cdb.conn!=null) {
                cdb.conn.close();
            }
            System.out.println("Close connect: Vacancy Fill Details");
        } catch (Exception e) {
            System.out.println("Error in close connection: " + e.getMessage());
        }
    }
}
